package CommunityController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import Connect.connector;

public class BCBoardDAO{
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ResultSet rs2 = null;
	public ArrayList<BCBoardBean> cm_bc_list_main(){
		conn = connector.getConnection();
		ArrayList<BCBoardBean> datas=new ArrayList<BCBoardBean>();
		try{
			
				
				String sql;	
				sql="select * from (select * from BCBOARD order by bc_date desc) a where bc_date limit 0,6";
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
			
			while(rs.next()){
				BCBoardBean bCBoardBean=new BCBoardBean();
				bCBoardBean.setId(rs.getInt("id"));
				bCBoardBean.setMembers_id(rs.getInt("members_id"));
				bCBoardBean.setBc_nick(rs.getString("bc_nick"));
				bCBoardBean.setBc_title(rs.getString("bc_title"));
				bCBoardBean.setBc_contents(rs.getString("bc_contents"));
				bCBoardBean.setBc_date(rs.getDate("bc_date"));
				bCBoardBean.setBc_viewcount(rs.getInt("bc_viewcount"));
				bCBoardBean.setRef(rs.getInt("ref"));
				bCBoardBean.setRe_step(rs.getInt("re_step"));
				bCBoardBean.setRe_level(rs.getInt("re_level"));
				datas.add(bCBoardBean);
				
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public ArrayList<BCBoardBean> cm_bc_list(HashMap param){
		conn = connector.getConnection();
		ArrayList<BCBoardBean> datas=new ArrayList<BCBoardBean>();
		try{
			
			if(param.get("searchword")==null || "".equals(param.get("searchword"))){
				String sql;	
				sql="select * from BCBOARD order by ref desc ,re_step limit ?,?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, (Integer)param.get("startNum"));
				pstmt.setInt(2, (Integer)param.get("pageSize"));
				rs=pstmt.executeQuery();
			}else {
				String sql;	
				sql="select * from BCBOARD where "+param.get("search") +" like ? order by ref desc ,re_step limit ?,?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, "%"+(String)param.get("searchword")+"%");
				pstmt.setInt(2, (Integer)param.get("startNum"));
				pstmt.setInt(3, (Integer)param.get("pageSize"));
				rs=pstmt.executeQuery();
			}
			while(rs.next()){
				BCBoardBean bCBoardBean=new BCBoardBean();
				bCBoardBean.setId(rs.getInt("id"));
				bCBoardBean.setMembers_id(rs.getInt("members_id"));
				bCBoardBean.setBc_nick(rs.getString("bc_nick"));
				bCBoardBean.setBc_title(rs.getString("bc_title"));
				bCBoardBean.setBc_contents(rs.getString("bc_contents"));
				bCBoardBean.setBc_date(rs.getDate("bc_date"));
				bCBoardBean.setBc_viewcount(rs.getInt("bc_viewcount"));
				bCBoardBean.setRef(rs.getInt("ref"));
				bCBoardBean.setRe_step(rs.getInt("re_step"));
				bCBoardBean.setRe_level(rs.getInt("re_level"));
				datas.add(bCBoardBean);
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public Integer bcTotalCount(HashMap param){
		conn = connector.getConnection();
		int count=0;
		try{
			
			String sql;
			if(param.get("searchword")==null || "".equals(param.get("searchword"))){
				sql="select count(*) cnt  from BCBOARD;";
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				if(rs.next()){
			    	count=rs.getInt("cnt");
			    }
				
			}else {
				
				sql="select count(*) cnt from BCBOARD where "+param.get("search") +" like ? ;";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, "%"+(String)param.get("searchword")+"%");
				rs=pstmt.executeQuery();
			}
			
			if(rs.next()){
		    	count=rs.getInt("cnt");
		    	System.out.println(count);
		    }
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return count;
	}
	
	public BCBoardBean cm_bc_read(int id){
		conn = connector.getConnection();
		BCBoardBean bCBoardBean=new BCBoardBean();
		try{
			
			String sql;	
			String sql2;
			sql="select * from BCBOARD where id=?";
			sql2="update BCBOARD set bc_viewcount=bc_viewcount+1 where id=?";
			pstmt=conn.prepareStatement(sql2);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
			
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				bCBoardBean.setBc_date(rs.getDate("bc_date"));
				bCBoardBean.setBc_contents(rs.getString("bc_contents"));
				bCBoardBean.setId(rs.getInt("id"));
				bCBoardBean.setMembers_id(rs.getInt("members_id"));
				bCBoardBean.setBc_nick(rs.getString("bc_nick"));
				bCBoardBean.setBc_title(rs.getString("bc_title"));
				bCBoardBean.setBc_viewcount(rs.getInt("bc_viewcount"));
				bCBoardBean.setRef(rs.getInt("ref"));
				bCBoardBean.setRe_step(rs.getInt("re_step"));
				bCBoardBean.setRe_level(rs.getInt("re_level"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return bCBoardBean;
	}
	
	public void cm_bc_write(BCBoardBean bCBoardBean){
		conn = connector.getConnection();
		try{
			
			String sql;	
			String sql2;
			String sql3;
			sql="insert into BCBOARD(id,members_id,bc_title,bc_nick,bc_date,bc_viewcount,bc_contents,re_step,re_level) values(null,6,?,?,Sysdate(),0,?,0,0)";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, bCBoardBean.getBc_title());
			pstmt.setString(2, bCBoardBean.getBc_nick());
			pstmt.setString(3, bCBoardBean.getBc_contents());
			pstmt.executeUpdate();
			
			sql2="select max(id) from BCBOARD";
			pstmt=conn.prepareStatement(sql2);
			rs=pstmt.executeQuery();
			BCBoardBean bCBoardBean1=new BCBoardBean();
			if(rs.next()){
				
				bCBoardBean1.setId(rs.getInt("max(id)"));
			}
			sql3="update BCBOARD set ref=? where id=?";
			pstmt=conn.prepareStatement(sql3);
			pstmt.setInt(1, bCBoardBean1.getId());
			pstmt.setInt(2, bCBoardBean1.getId());
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void cm_bc_modify(BCBoardBean bCBoardBean){
		conn = connector.getConnection();
		try{
			
			String sql1;		
			sql1 = "update BCBOARD set bc_title=?, bc_contents=? where id=?";
			 pstmt=conn.prepareStatement(sql1);
			 pstmt.setString(1, bCBoardBean.getBc_title());
			 pstmt.setString(2, bCBoardBean.getBc_contents());
			 pstmt.setInt(3, bCBoardBean.getId());
			 pstmt.executeUpdate();
	
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		
	}
	
	public void cm_bc_delete(int id){
		conn = connector.getConnection();
		try{
			
			String sql;
			sql="delete from BCBOARD where id=?";
			 pstmt=conn.prepareStatement(sql);
			 pstmt.setInt(1, id);
			 pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	public Integer young_level(String mem_nick){
		conn = connector.getConnection();
		int level=0;
		try{
			
			String sql;	
			sql = "select mem_level from MEMBERS where mem_nick=?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mem_nick);
			rs=pstmt.executeQuery();
			if(rs.next()){
				level=rs.getInt("mem_level");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return level;
	}
	
	public void cm_bc_reple(BCBoardBean bCBoardBean){
		conn = connector.getConnection();
		ArrayList<BCBoardBean> datas=new ArrayList<BCBoardBean>();
		//BCBoardBean freeBoardBean1=new BCBoardBean();
		try{
			String sql1;
			String sql2;
			String sql3;
			String sql4;
			String sql5;
			int rstep1=bCBoardBean.getRe_step();
			int rstep2=0;
			int rlevel=1;
			if(bCBoardBean.getRe_step()==0){
				 sql4="select count(*) cnt from BCBOARD where ref=?";
				 	pstmt=conn.prepareStatement(sql4);
				 	pstmt.setInt(1, bCBoardBean.getRef());
					rs=pstmt.executeQuery();
					if(rs.next()){
						rstep2=rs.getInt("cnt");
					}
			}else{
				
				rlevel=bCBoardBean.getRe_level()+1;
				sql1="select id,re_level,ref,re_step from  BCBOARD where ref=? and re_level=?";
				pstmt=conn.prepareStatement(sql1);
				pstmt.setInt(1, bCBoardBean.getRef());
				pstmt.setInt(2, bCBoardBean.getRe_level()+1);
				rs=pstmt.executeQuery();
				if(rs.next()){
					sql5="select id,re_level,ref,re_step from BCBOARD where ref=? and re_step=? and re_level=?";
					pstmt=conn.prepareStatement(sql5);
					pstmt.setInt(1, bCBoardBean.getRef());
					pstmt.setInt(2, bCBoardBean.getRe_step()+1);
					pstmt.setInt(3, bCBoardBean.getRe_level()+1);
					rs2=pstmt.executeQuery();
					if(rs2.next()){ 
						sql1="select id,re_level,ref,re_step from BCBOARD where ref=? and re_step>? and re_level=?";
						pstmt=conn.prepareStatement(sql1);
						pstmt.setInt(1, bCBoardBean.getRef());
						pstmt.setInt(2, bCBoardBean.getRe_step());
						pstmt.setInt(3, bCBoardBean.getRe_level()+1);
						rs=pstmt.executeQuery();
					
						while(rs.next()){
						
							rstep1=rstep1+1;
							BCBoardBean bCBoardBean2=new BCBoardBean();
							bCBoardBean2.setRe_step(rs.getInt("re_step"));
							rstep2=bCBoardBean2.getRe_step();
							datas.add(bCBoardBean2);
							
							if(datas.size()<2){
								rstep2=rstep2+1;
							}else{
								
								if(rstep1!=rstep2){
									rstep2=rstep2-1;
									break;
								}
							}
						}
					}else{
						rstep2=bCBoardBean.getRe_step()+1;
					}
					
					
				}else{
					rstep2=bCBoardBean.getRe_step()+1;
				}
			}
				sql3="update BCBOARD set re_step=re_step+1 where ref=? and re_step>=?";
				pstmt=conn.prepareStatement(sql3);
				pstmt.setInt(1, bCBoardBean.getRef());
				pstmt.setInt(2, rstep2);
				pstmt.executeUpdate();
				
				sql2="insert into BCBOARD(id,members_id,bc_title,bc_nick,bc_date,bc_viewcount,bc_contents,ref,re_step,re_level) values(null,6,?,?,Sysdate(),0,?,?,?,?)";
				pstmt=conn.prepareStatement(sql2);
				pstmt.setString(1, bCBoardBean.getBc_title());
				pstmt.setString(2, bCBoardBean.getBc_nick());
				pstmt.setString(3, bCBoardBean.getBc_contents());
				pstmt.setInt(4, bCBoardBean.getRef());
				pstmt.setInt(5,rstep2 );
				pstmt.setInt(6,rlevel );
				pstmt.executeUpdate();
		
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);;
		}
	}
	
}
